﻿// License: The MIT License (MIT) Copyright (c) 2011..2012 Barend Gehrels

// Permission is hereby granted, free of charge, to any person obtaining a
// copy of this software and associated documentation files (the "Software"),
// to deal in the Software without restriction, including without limitation
// the rights to use, copy, modify, merge, publish, distribute, sublicense,
// and/or sell copies of the Software, and to permit persons to whom the
// Software is furnished to do so, subject to the following conditions:

// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
// FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using Stormy;

namespace GrooveSharp
{
    public class ItemNotFoundException : Exception
    {
    }

    static class Db
    {
        private static Connection m_Connection = null;
        private static int m_userId = 0;

        private static string m_user = null;

        public static string UserName 
        { 
            get 
            {
                if (m_user == null)
                {
                    m_user = Environment.UserName;
                }
                return m_user; 
            } 
            set
            {
                m_user = value; 
            } 
        }

        static private string GetUserDataPath()
        {
            string dir = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
            dir = Path.Combine(dir, "GrooveSharp");
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            return dir;
        }

        public static int UserId()
        {
            return m_userId;
        }

        private static void RegisterMappers()
        {
            Orm.Register<Album>(new AlbumMapper());
            Orm.Register<Artist>(new ArtistMapper());
            Orm.Register<Flag>(new FlagMapper());
            Orm.Register<Genre>(new GenreMapper());
            Orm.Register<Track>(new TrackMapper());
            Orm.Register<TrackRating>(new TrackRatingMapper());
            Orm.Register<Play>(new PlayMapper());
            Orm.Register<Setting>(new SettingMapper());

            Orm.Register<GroovePath>(new GroovePathMapper());

            Orm.Register<CollectionItem>(new CollectionItemMapper());

            Orm.Register<int>(new IntegerMapper());
            Orm.Register<string>(new StringMapper());
        }


        private static void CreateDataModel()
        {
            App.Log.Info("Create Groove# Data Model");

            m_Connection.Execute(
@"

-- drop table albums;
-- drop table artists;
-- drop table genres;
-- drop table tracks;

CREATE TABLE artists ( 
    artist_id   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    artist_name TEXT,
    artist_sortname TEXT
);

CREATE TABLE albums ( 
    album_id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    album_title     TEXT,
    album_sorttitle TEXT,
    artist_id       INTEGER,
    FOREIGN KEY(artist_id) REFERENCES artists(artist_id) ON DELETE RESTRICT
);

CREATE TABLE genres ( 
    genre_id    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    genre_name TEXT 
);

CREATE TABLE paths ( 
    path_id     INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    path_folder TEXT,
    is_active integer
);


CREATE TABLE tracks ( 
    track_id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    path_id        INTEGER,
    folder_name    NVARCHAR( 255 ),
    file_name      NVARCHAR( 255 ),
    file_size      INTEGER,
    track_title    TEXT,
    track_sorttitle TEXT,
    track_year     INTEGER,
    disc_number    INTEGER,
    track_number   INTEGER,
    track_count    INTEGER,
    track_duration TEXT,
    itunes_id      TEXT, -- iTunes persistent id for synchronization with XML's (rating/playdates)
    album_id       INTEGER,
    artist_id      INTEGER,
    is_deleted     INTEGER,
    FOREIGN KEY(album_id) REFERENCES albums(album_id) ON DELETE RESTRICT,
    FOREIGN KEY(artist_id) REFERENCES artists(artist_id) ON DELETE RESTRICT
    FOREIGN KEY(path_id) REFERENCES paths(path_id) ON DELETE RESTRICT
);
 
-- Windows users:
CREATE TABLE users ( 
    user_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    user_name TEXT not null
);


CREATE TABLE track_flags ( 
    track_flag_id          INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    track_flag_name        NVARCHAR( 255 ) NOT NULL,
    track_flag_explanation TEXT,
    is_active              INTEGER
);


CREATE TABLE link_track_flag ( 
    link_track_flag_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    track_id integer not null,
    user_id integer not null,
    track_flag_id integer not null,
    is_active integer,
    FOREIGN KEY(track_id) REFERENCES tracks(track_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY(track_flag_id) REFERENCES track_flags(track_flag_id) ON DELETE CASCADE
);


CREATE TABLE ratings ( 
    rating_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    user_id integer,
    track_id integer not null,
    rating_value integer not null,
    rating_date datetime,
    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY(track_id) REFERENCES tracks(track_id) ON DELETE CASCADE
);

CREATE TABLE plays ( 
    play_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    user_id integer not null,
    play_date datetime,
    play_machine TEXT,
    play_duration TEXT,
    track_id integer not null,
    FOREIGN KEY(track_id) REFERENCES tracks(track_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE settings ( 
    setting_id    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    setting_key   INTEGER NOT NULL,
    setting_value TEXT
);

 
-- Insert a few sample flags
insert into track_flags(track_flag_name,is_active) values('Single',1);
insert into track_flags(track_flag_name,is_active) values('Hit',1);
insert into track_flags(track_flag_name,is_active) values('Danceable',1);
insert into track_flags(track_flag_name,is_active) values('Instrumental',1);
insert into track_flags(track_flag_name,is_active) values('Live',1);
insert into track_flags(track_flag_name,is_active) values('Bonus',1);
insert into track_flags(track_flag_name,is_active) values('Mixed',1);

insert into settings(setting_key, setting_value) values(1, 1); -- announcer

");
        }

        // THIS IS TEMPORARY
        private static void UpdateDataModel()
        {
            try
            {
                m_Connection.Execute("alter table track_flags add column is_active INTEGER");
                m_Connection.Execute("alter table albums add column album_sorttitle TEXT");
                m_Connection.Execute("alter table tracks add column track_sorttitle TEXT");
                App.Log.Info("Update Groove# Data Model");
            }
            catch (Exception) {}
        }


        public static T SelectFirstOrDefault<T>(string sql, IEnumerable<StormyParameter> parameters) where T : new()
        {
            foreach (T id in Db.Connection().Select<T>(sql, parameters))
            {
                return id;
            }
            return new T();
        }

        public static T SelectFirstOrDefault<T>(string sql, T defaultValue)
        {
            foreach (T id in Db.Connection().Select<T>(sql))
            {
                return id;
            }
            return defaultValue;
        }

        public static T SelectFirstOrThrow<T>(string sql, IEnumerable<StormyParameter> parameters)
        {
            foreach (T id in Db.Connection().Select<T>(sql, parameters))
            {
                return id;
            }
            throw new ItemNotFoundException();
        }

        public static T SelectFirstOrThrow<T>(string sql)
        {
            foreach (T id in Db.Connection().Select<T>(sql))
            {
                return id;
            }
            throw new ItemNotFoundException();
        }

        private static void SelectOrInsertUser()
        {
            var pars = new List<StormyParameter>{ Db.CreateParameter(DbType.String, "@name", UserName) };
            m_userId = SelectFirstOrDefault<int>("select user_id from users where user_name=@name", pars);
            if (m_userId == 0)
            {
                Connection().Execute("insert into users(user_name) values(@name)", pars);
                m_userId = SelectFirstOrDefault<int>("select user_id from users where user_name=@name", pars);
            }
        }

        public static string DefaultMusicPath()
        {
            // Usually (Windows 7): c:\users\Barend\Music, if user is called "Barend"
            return String.Format(@"c:\Users\{0}\Music\", UserName);
        }

        private static void InsertDefaultPathIfNeeded()
        {
            string defaultFolder = DefaultMusicPath();
#if DEBUG
            //defaultFolder = @"c:\temp\music5";
#endif
            var pars = new List<StormyParameter> { Db.CreateParameter(DbType.String, "@folder", defaultFolder) };
            int count = SelectFirstOrDefault<int>("select count(1) from paths where path_folder=@folder", pars);
            if (count == 0)
            {
                Connection().Execute("insert into paths(path_folder, is_active) values(@folder, 1)", pars);

#if DEBUG
                // Create non-existing folder
                pars[0] = Db.CreateParameter(DbType.String, "@folder", @"c:\temp\music_wrong");
                Connection().Execute("insert into paths(path_folder, is_active) values(@folder, 1)", pars);
#endif

            }
        }

        public static Connection Connection()
        {
            if (m_Connection == null)
            {
                RegisterMappers();

                bool doCreate = false;
                string filename = GetUserDataPath() + @"\GrooveSharp.db";
#if DEBUG
                filename = GetUserDataPath() + @"\GrooveSharp_DEBUG.db";
#endif
                if (!File.Exists(filename))
                {
                    // http://stackoverflow.com/questions/5106771/how-to-create-database-and-add-2-table-in-sqlite
                    SQLiteConnection.CreateFile(filename);
                    doCreate = true;
                }

                App.Log.Info("Connect to SQLite " + filename);


                m_Connection = new Stormy.Connection(new SQLiteConnection(String.Format("Data Source={0}", filename)));
                m_Connection.Execute("PRAGMA foreign_keys = ON");

                if (doCreate)
                {
                    CreateDataModel();
                }
                else
                {
                    UpdateDataModel();
                }
                SelectOrInsertUser();
                InsertDefaultPathIfNeeded();

            }
            return m_Connection;
        }

        // -------------------------------------
        // utility methods (will be moved later)
        // -------------------------------------
        public static int TrackIdFromFileName(string fileName)
        {
            // Must use parameters because of possible quotes in the title
            return SelectFirstOrDefault<int>
                (
                    "select track_id from tracks where file_name like @fname",
                    new List<StormyParameter>() 
                            { 
                                Db.CreateParameter(DbType.String, "@fname", System.IO.Path.GetFileName(fileName))
                            }
                );
        }

        public static StormyParameter CreateParameter(DbType type, string name, object value)
        {
            return new StormyParameter() { Type = type, Name = name, Value = value };
        }

        public static IDbDataParameter CreateParameter(IDbCommand command, DbType type, string name, object value)
        {
            var result = command.CreateParameter();
            result.ParameterName = name;
            result.DbType = type;
            result.Value = value;
            return result;
        }

    }
}
